package com.sl.au.repository;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;

import com.sl.au.entity.EXDEGiveInvoiceHistoryWithContract;

/**
 * 合同开票历史
 * @author yry
 * 2017-02-20
 */
@Transactional(readOnly = true)
public interface EXDEPayBillingHistoryRepository extends JpaRepository<EXDEGiveInvoiceHistoryWithContract, String> {
	/**
	 * 查询开票历史的SQL
	 * @param superior //部门
	 * @param pageable //分页
	 * @return
	 */
	@Query("select g from EXDEGiveInvoiceHistoryWithContract g where cSuperior = ?1")
	Page<EXDEGiveInvoiceHistoryWithContract> findAllTrance(String superior,Pageable pageable);
	
	/**
	 * 开票历史信息条数
	 * @param superior
	 * @return
	 */
	@Query("select count(*) as num from EXDEGiveInvoiceHistoryWithContract g where cSuperior = ?1")
	long findAllTrancegetCount(String superior);
	
	/**
	 * 开票历史搜索
	 * @param superior //部门
	 * @param key //搜索内容
	 * @param pageable
	 * @return
	 */
	@Query("select g from EXDEGiveInvoiceHistoryWithContract g where cSuperior = ?1 and (g.cCustomerName like '%'+?2+'%'"
			+ " or g.cProjectName like '%'+?2+'%' or g.cAreaName like '%'+?2+'%'"
			+ " or g.cIsIncome like '%'+?2+'%' or g.cMemo like '%'+?2+'%')")
	Page<EXDEGiveInvoiceHistoryWithContract> findAllTrance(String superior,String key, Pageable pageable);
	
	/**
	 * 开票历史搜索条数
	 * @param superior //部门
	 * @param key //查询条件
	 * @return
	 */
	@Query("select count(*) as num from EXDEGiveInvoiceHistoryWithContract g where cSuperior = ?1 and (g.cCustomerName like '%'+?2+'%'"
			+ " or g.cProjectName like '%'+?2+'%' or g.cAreaName like '%'+?2+'%'"
			+ " or g.cIsIncome like '%'+?2+'%' or g.cMemo like '%'+?2+'%')")
	long findAllTrancegetCount(String superior,String key);

}
